﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Npgsql;

namespace SpeedTestReports.Web
{
    public enum ConnectionState
    {
        Connected,
        Disconnected
    }

    public class Data
    {
        private string connectionString = String.Empty;
        //private SQLiteConnection conn = null;
        private NpgsqlConnection conn = null;
        private ConnectionState state = ConnectionState.Disconnected;

        public Data()
        {

        }

        public Data(string connectionString)
        {
            if (!String.IsNullOrEmpty(connectionString)) { this.connectionString = connectionString; }
        }

        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }

        public ConnectionState State
        {
            get
            {
                return state;
            }
        }

        public bool Connect(string connectionString)
        {
            this.connectionString = connectionString;
            return Connect();
        }

        public bool Connect()
        {
            if (String.IsNullOrEmpty(connectionString))
            {
                return false;	//TODO: should throw an exception
            }

            try
            {
                conn = new NpgsqlConnection(connectionString);
                conn.Open();
                this.state = ConnectionState.Connected;
            }
            catch (Exception ex)
            {
                //TODO: Error handling
                if (conn != null) { conn.Close(); }
                return false;
            }

            return true;
        }

        public void Disconnect()
        {
            if (conn != null && state != ConnectionState.Disconnected)
            {
                conn.Close();
            }
        }

        public bool Authenticate(string userName, string password)
        {
            SelectionValue nameVal = new SelectionValue("USERNAME", userName, String.Empty);
            SelectionValue pwdVal = new SelectionValue("PASSWORD", password, String.Empty);
            SelectionValue[] namedParams = new SelectionValue[2] { nameVal, pwdVal };

            string sqlStatement = "SELECT count(id) FROM Users WHERE UserName = :USERNAME and Password = :PASSWORD";

            object obj = GetSingleValueByParams(sqlStatement, namedParams);
            if (obj != DBNull.Value)
            {
                long userCount = (long)obj;

                if (userCount > 0)
                {
                    return true;
                }
                else { return false; }
            }
            else { return false; }
        }

        public bool CheckUserNameAvailability(string userName)
        {
            SelectionValue[] namedParams = new SelectionValue[1] {new SelectionValue("USERNAME", userName, String.Empty)};

            string sqlStatement = "SELECT count(id) FROM Users WHERE username = :USERNAME";
            object obj = GetSingleValueByParams(sqlStatement, namedParams);
            if (obj != DBNull.Value)
            {
                long userCount = (long)obj;

                if (userCount == 0)
                {
                    return true;
                }
                else { return false; }
            }
            else { return false; }
        }

        public DataTable GetUserByUserName(string userName)
        {
            SelectionValue[] namedParams = new SelectionValue[1] { new SelectionValue("USERNAME", userName, String.Empty) };
            string sqlStatement = "SELECT * FROM users WHERE active = 'true' AND username = :USERNAME";
            return GetTableByParamSql(sqlStatement, namedParams);
        }

        public bool InsertUser(string name, string email, string userName, string password)
        {
            SelectionValue nameValue = new SelectionValue("NAME", name, String.Empty);
            SelectionValue emailValue = new SelectionValue("EMAIL", email, String.Empty);
            SelectionValue userValue = new SelectionValue("USERNAME", userName, String.Empty);
            SelectionValue pwdValue = new SelectionValue("PASSWORD", password, String.Empty);
            SelectionValue roleValue = new SelectionValue("ROLEID", "2", String.Empty);
            SelectionValue activeValue = new SelectionValue("ACTIVE", "true", String.Empty);

            SelectionValue[] namedParams = new SelectionValue[6] { nameValue, emailValue, userValue, pwdValue, roleValue, activeValue };

            string sqlStatement = "INSERT INTO Users (Name, Email, UserName, Password, RoleID, Active) ";
            sqlStatement += "VALUES ( :NAME, :EMAIL, :USERNAME, :PASSWORD, :ROLEID, :ACTIVE ) RETURNING id; ";

            long userID = InsertDataByParams(sqlStatement, namedParams);

            if (userID > 0)
            {
                return true;
            }

            return false;
        }

        public DataTable GetReportList()
        {
            string sqlStatement = "SELECT reports.id, reports.reportname, repcategories.description AS category";
            sqlStatement += " FROM reports";
            sqlStatement += " LEFT JOIN repcategories on reports.categoryid = repcategories.id";
            sqlStatement += " WHERE dashboard = false";
            sqlStatement += " ORDER BY repcategories.description ASC, reports.reportname ASC";
            
            return GetData(sqlStatement);
        }

        public DataTable GetSelectionsByReportID(string id)
        {
            string sqlStatement = "SELECT id, selectionsql, selectiontype, valuemember, displaymember, parametername, label, defaultvalue, isrequired, sequence FROM repselections";
            sqlStatement += " WHERE reportid = " + id + " ORDER BY sequence ASC, id ASC";

            return GetData(sqlStatement);
        }

        public DataTable GetReportSections(string id)
        {
            string sqlStatement = "SELECT * FROM repsections WHERE reportid = " + id;
            return GetData(sqlStatement);
        }

        public DataTable GetReportByID(string id)
        {
            string sqlStatement = "SELECT * FROM reports WHERE id = " + id;
            return GetData(sqlStatement);
        }

        public long GetDashboardReportID()
        {
            string sqlStatement = "SELECT id FROM reports WHERE dashboard = true";
            object obj = GetSingleValue(sqlStatement);
            if (obj != DBNull.Value)
            {
                int dashboardID = (int)obj;

                return (long)dashboardID;
            }

            return -1;
        }
 
        private string SingleQuote(string input)
        {
            string output = "'" + input + "'";
            return output;
        }

        public DataTable GetTableBySql(string sqlStatement)
        {
            return GetData(sqlStatement);
        }

        public DataTable GetTableByParamSql(string sqlStatement, SelectionValue[] selValues)
        {
            DataSet ds = null;

            if (selValues == null)
            {
                ds = GetDataSet(sqlStatement);
            }
            else
            {
                ds = GetDataSetByParams(sqlStatement, selValues);
            }
            
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public DataSet GetSetByParamSql(string sqlStatement, SelectionValue[] selValues)
        {
            return GetDataSetByParams(sqlStatement, selValues);
        }

        private DataTable GetData(string sqlStatement)
        {
            DataTable dt = new DataTable();
           
            NpgsqlCommand cmd = null;
            NpgsqlDataReader reader = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = conn.CreateCommand();
                    cmd.CommandText = sqlStatement;
                    reader = cmd.ExecuteReader();

                    dt.Load(reader);

                    reader.Close();
                }
                catch (Exception ex)
                {
                    if (reader != null && reader.IsClosed == false) { reader.Close(); }
                }
            }

            return dt;
        }

        private long InsertData(string sqlStatement)
        {
            NpgsqlCommand cmd = null;
            object ido = null;
            long id = -1;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = conn.CreateCommand();
                    cmd.CommandText = sqlStatement;
                    ido = cmd.ExecuteScalar();

                    if (ido != null)
                    {
                        id = Int64.Parse(ido.ToString());
                    }
                }
                catch (Exception ex)
                {
                    //error
                    return -1;
                }
            }

            return id;
        }

        public long InsertDataByParams(string sqlStatement, SelectionValue[] namedParams)
        {
            NpgsqlCommand cmd = null;
            object ido = null;
            long id = -1;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = new NpgsqlCommand(sqlStatement, conn);

                    for (int i = 0; i < namedParams.Length; i++)
                    {
                        //cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
                        cmd.Parameters.Add(new NpgsqlParameter(namedParams[i].Name, namedParams[i].Value));
                        //cmd.Parameters[i].Value = selValues[i].Value;
                    }

                    ido = cmd.ExecuteScalar();

                    if (ido != null)
                    {
                        id = Int64.Parse(ido.ToString());
                    }
                }
                catch (Exception ex)
                {
                    //error
                    return -1;
                }
            }

            return id;
        }

        private object GetSingleValue(string sqlStatement)
        {
            NpgsqlCommand cmd = null;
            object returnVal = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = conn.CreateCommand();
                    cmd.CommandText = sqlStatement;
                    returnVal = cmd.ExecuteScalar();
                }
                catch
                {
                    //error
                    return null;
                }
            }

            return returnVal;
        }

        public object GetSingleValueByParams(string sqlStatement, SelectionValue[] namedParams)
        {
            NpgsqlCommand cmd = null;
            object returnVal = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = new NpgsqlCommand(sqlStatement, conn);

                    for (int i = 0; i < namedParams.Length; i++)
                    {
                        //cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
                        cmd.Parameters.Add(new NpgsqlParameter(namedParams[i].Name, namedParams[i].Value));
                        //cmd.Parameters[i].Value = selValues[i].Value;
                    }

                    returnVal = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    return null;
                }
            }

            return returnVal;
        }

        private DataSet GetDataSet(string sqlStatement)
        {
            DataSet ds = new DataSet();

            NpgsqlCommand cmd = null;
            NpgsqlDataAdapter adapter = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = conn.CreateCommand();
                    cmd.CommandText = sqlStatement;

                    adapter = new NpgsqlDataAdapter(cmd);

                    adapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    if (adapter != null) { adapter.Dispose(); }
                }
            }

            return ds;
        }

        private DataSet GetDataSetByParams(string sqlStatement, SelectionValue[] selValues)
        {
            DataSet ds = new DataSet();

            NpgsqlCommand cmd = null;
            NpgsqlDataAdapter adapter = null;

            if (conn != null & this.State == ConnectionState.Connected)
            {
                try
                {
                    cmd = new NpgsqlCommand(sqlStatement, conn);

                    for (int i = 0; i < selValues.Length; i++)
                    {
                        //cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, DbType.String));
                        cmd.Parameters.Add(new NpgsqlParameter(selValues[i].Name, selValues[i].Value));
                        //cmd.Parameters[i].Value = selValues[i].Value;
                    }
                   
                    adapter = new NpgsqlDataAdapter(cmd);

                    adapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    if (adapter != null) { adapter.Dispose(); }
                }
            }

            return ds;

        }

    } //end of class
}
